/*
* Copyright (C) 2012 Manuel Pérez
*
* This file is part of OpenSudoku.
*
* OpenSudoku is free software: you can redistribute it and/or modify
* it under the terms of the GNU General Public License as published by
* the Free Software Foundation, either version 3 of the License, or
* (at your option) any later version.
*
* OpenSudoku is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
* GNU General Public License for more details.
*
* You should have received a copy of the GNU General Public License
* along with OpenSudoku. If not, see <http://www.gnu.org/licenses/>.
*
*/
package dBasea;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import modeloa.Bezero;

public class Bezeroa {

	public List<String> getAseguruTipoDeskripzioa() throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT deskripzioa FROM AseguruTipoa";
		ResultSet rs = dbk.executeSQL(query);

		List<String> lista = new ArrayList<String>();
		while (rs.next()) {
			lista.add(rs.getString(1));
		}
		rs.close();

		return lista;
	}

	public boolean existitzenDa(String nif) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();

		String query = "SELECT * FROM BezeroAseguratua WHERE bezeroID='" + nif
				+ "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean aurki = rs.next();
		return aurki;
	}

	public void bezSortu(String nif, String izena, String abizenak,
			String helbidea, Integer telefonoa, String asegurua)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "INSERT INTO BezeroAseguratua (bezeroID, izena, abizenak, helbidea, telefonoa, aseguruTipoKodigoa)"
				+ " VALUES ('"
				+ nif
				+ "','"
				+ izena
				+ "', '"
				+ abizenak
				+ "', '"
				+ helbidea
				+ "',"
				+ telefonoa
				+ ", (select kodigoa from AseguruTipoa where deskripzioa = '"
				+ asegurua + "'))";

		dbk.executeSQL(query);

	}

	public void bezEzabatu(String nif) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "DELETE FROM BezeroAseguratua WHERE bezeroID='" + nif
				+ "'";
		dbk.executeSQL(query);
	}

	public Bezero getBezeroDatuak(String nif) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT * FROM BezeroAseguratua b, AseguruTipoa a WHERE b.bezeroID='"
				+ nif + "' and b.aseguruTipoKodigoa = a.kodigoa";
		ResultSet rs = dbk.executeSQL(query);
		Bezero bezeroa = null;
		if (rs.next()) {
			String NAN = rs.getString("bezeroID");
			String izena = rs.getString("izena");
			String abizenak = rs.getString("abizenak");
			String helbidea = rs.getString("helbidea");
			int telefonoa = rs.getInt("telefonoa");
			String aseguruTipo = rs.getString("deskripzioa");

			bezeroa = new Bezero(NAN, izena, abizenak, helbidea, telefonoa,
					aseguruTipo);
		}
		rs.close();
		return bezeroa;
	}

	public void bezAldatu(String nif, String izena, String abizenak,
			String helbidea, Integer telefonoa, String asegurua)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "UPDATE BezeroAseguratua SET izena='"
				+ izena
				+ "', abizenak = '"
				+ abizenak
				+ "', helbidea = '"
				+ helbidea
				+ "', telefonoa ="
				+ telefonoa
				+ ", aseguruTipoKodigoa = (select kodigoa from AseguruTipoa where deskripzioa = '"
				+ asegurua + "') " + "WHERE bezeroID = '" + nif + "'";
		dbk.executeSQL(query);
	}

	public boolean existitzenDaMatrikulaBezeroarekin(String mat, String nif)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "SELECT * FROM bezeroIbilgailuak WHERE matrikula='"
				+ mat + "' and bezeroId='" + nif + "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean aurki = rs.next();
		rs.close();
		return aurki;
	}

	public boolean aseguruaExistitzenDa(String nif, String matrikula)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();

		String query = "SELECT * FROM BezeroIbilgailuak WHERE bezeroId='" + nif
				+ "' and matrikula = '" + matrikula + "'";
		ResultSet rs = dbk.executeSQL(query);
		boolean existitzen = rs.next();
		rs.close();
		return existitzen;
	}

	public long urteaLortu(String id, String matrikula) throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		String query = "Select max(urtea) from BezeroIbilgailuak where bezeroId = '"
				+ id + "' and matrikula ='" + matrikula + "'";
		ResultSet rs = dbk.executeSQL(query);
		long data = 0;
		if (rs.next()) {
			data = rs.getLong(1);
		}
		return data;
	}

	@SuppressWarnings("deprecation")
	public Date bezBerriztu(String matrikula, String nif, String asegurua)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		long data = urteaLortu(nif, matrikula);
		Date dateData = new Date(data);
		dateData.setYear(dateData.getYear() + 1);

		String query = "INSERT INTO BezeroIbilgailuak ( matrikula, bezeroID, aseguruTipoKodigoa, urtea)"
				+ " VALUES ('"
				+ matrikula
				+ "','"
				+ nif
				+ "',  (Select kodigoa from AseguruTipoa where deskripzioa = '"
				+ asegurua + "'), " + dateData.getTime() + ")";

		dbk.executeSQL(query);
		return dateData;
	}

	@SuppressWarnings("deprecation")
	public Date bezBerriztu2(String matrikula, String nif, String asegurua)
			throws SQLException {
		DatuBasea dbk = DatuBasea.getInstance();
		// long data=urteaLortu(nif, matrikula);
		Date dateData = new Date();
		dateData.setYear(dateData.getYear() + 1);

		String query = "INSERT INTO BezeroIbilgailuak ( matrikula, bezeroID, aseguruTipoKodigoa, urtea)"
				+ " VALUES ('"
				+ matrikula
				+ "','"
				+ nif
				+ "',  (Select kodigoa from AseguruTipoa where deskripzioa = '"
				+ asegurua + "'), " + dateData.getTime() + ")";

		dbk.executeSQL(query);
		return dateData;
	}
}